package cn.shaines.filesystem.util;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import java.io.*;
import java.nio.charset.StandardCharsets;
import javax.sql.DataSource;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

/**
 * @author zhoutanxin@gmail.com
 * @date 2024/3/21 22:47
 */
@Component
public class DatabaseUtil {

    private final DataSource dataSource;
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DatabaseUtil(DataSource dataSource, JdbcTemplate jdbcTemplate) {
        this.dataSource = dataSource;
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 创建一个数据库
     * @param databaseName
     * @throws SQLException
     */
    public void createDatabase(String databaseName) throws SQLException {
        String createDatabaseQuery = "CREATE DATABASE IF NOT EXISTS " + databaseName;
        try (Connection connection = dataSource.getConnection()) {
            Statement statement = connection.createStatement();
            statement.execute(createDatabaseQuery);
        }
    }

    /**
     * 删除一个数据库
     * @param databaseName
     * @throws SQLException
     */
    public void deleteDatabase(String databaseName) throws SQLException {
        String dropDatabaseQuery = "DROP DATABASE IF EXISTS " + databaseName;
        try (Connection connection = dataSource.getConnection()) {
            Statement statement = connection.createStatement();
            statement.execute(dropDatabaseQuery);
        }
    }

    /**
     * 得到某个数据库下的所有表明
     * @param siteId
     * @return
     * @throws SQLException
     */
    public List<String> getAllTables(String siteId) throws SQLException {
        Connection connection=dataSource.getConnection();
        String oriCatalog=connection.getCatalog();
        connection.setCatalog(siteId);
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getTables(connection.getCatalog(), null, null, new String[] {"TABLE"});

            List<String> tableNames = new ArrayList<>();
            while (resultSet.next()) {
                tableNames.add(resultSet.getString("TABLE_NAME"));
            }

            return tableNames;
        } finally {
            connection.setCatalog(oriCatalog);
            connection.close();
        }
    }

    /**
     * 根据提供的数据库，执行sql文件
     * @param siteId
     * @param sqlScriptPath
     */
    public void executeSqlScript(String siteId,String sqlScriptPath) {
        try {
            Connection connection=dataSource.getConnection();
            String oriCatalog=connection.getCatalog();
            connection.setCatalog(siteId);
            Resource resource = new ClassPathResource(sqlScriptPath);
            ScriptUtils.executeSqlScript(connection, resource);
            connection.setCatalog(oriCatalog);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("Error executing SQL script", e);
        }
    }

    /**
     * 将某个数据库的表导出成sql文件
     * @param siteId
     * @param filePath
     * @throws IOException
     */
    public void exportSqlFile(String siteId, String filePath) {
            File file = new File(filePath);
            FileWriter writer = null;
            Connection connection = null;
            try {
                writer = new FileWriter(file, true);
                connection = dataSource.getConnection();
                String oriCatalog=connection.getCatalog();
                connection.setCatalog(siteId);
                List<String> tables =getAllTables(siteId);
                Statement statement = connection.createStatement();
                for (String tableName:tables) {
                    writer.write("DROP TABLE IF EXISTS `" + tableName + "`; \n");

                    ResultSet resultSetColumns = statement.executeQuery("SHOW CREATE TABLE `" + tableName + "`");
                    if (resultSetColumns.next()) {
                        String createTableSql = resultSetColumns.getString(2);
                        writer.write(createTableSql + ";\n");
                    }

                    resultSetColumns = statement.executeQuery("SELECT * FROM `" + tableName + "`");
                    ResultSetMetaData metaData = resultSetColumns.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    while (resultSetColumns.next()) {
                        writer.write("INSERT INTO `" + tableName + "` VALUES (");
                        for (int i = 1; i <= columnCount; i++) {
                            if (i > 1) {
                                writer.write(", ");
                            }
                            Object value = resultSetColumns.getObject(i);
                            if (value instanceof String) {
                                writer.write("'" + resultSetColumns.getString(i) + "'");
                            } else {
                                writer.write(value.toString());
                            }
                        }
                        writer.write(");\n");
                    }
                }
                connection.setCatalog(oriCatalog);
                writer.close();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (SQLException throwables){
                throwables.printStackTrace();
            }
    }


}
